﻿Imports System.Xml
Imports System.Reflection
Imports Mysoft.Map.Workflow.Types
Imports Mysoft.Map.Workflow.BusinessDomain
Imports Mysoft.Map.Data
Imports System.Web
Imports System.Text


Public Class DJApprove
    Inherits BusinessDomainBase

    Private _businessGUID As String     '销售变更申请GUID
    Private _xml As XmlDocument = New XmlDocument

    Public Sub New()
        MyBase.New("/PubProject/workflow/slxt/DJApprove.xml")
        _xml.LoadXml(MyBase.XML)
    End Sub

    '属性：底价调价计划GUID
    Public Overrides Property BusinessGUID() As String
        Get
            Return _businessGUID
        End Get
        Set(ByVal Value As String)
            _businessGUID = Value
        End Set
    End Property

    '属性：XML
    '最终XML格式：

    Public Overrides Property XML() As String
        Get
            Return _xml.OuterXml
        End Get
        Set(ByVal Value As String)
            _xml.LoadXml(Value)
        End Set
    End Property

    '功能：取底价审批数据，填充XML
    Public Overrides Function LoadData() As Integer
        Dim strSQL, strBldGUID, strSQLLastPlan, strLastPlanGUID, strTotalChg, strPriceChg As String
        Dim strHjZts, strHjZBldArea, strHjTs, strHjBldArea, strHjPrice, strHjLastArea, strHjLastTotal, strHjTotal, strHjPriceChg, strHjTotalChg As String
        strHjTotalChg = "0.00"
        strHjPriceChg = "0.00"
        strHjLastArea = "0.00"
        strHjLastTotal = "0.00"
        Dim dtTemp As DataTable
        Dim dtDetail, dtLastPlan, dtBldGUID As DataTable
        Dim i, z As Integer
        Dim strSQLDetail As New StringBuilder
        Dim PubMng As New PublicMNG

        '获取表单基础信息
        strSQL = "SELECT STP.PlanName,BU.BUName,P.ProjName,STP.Remark FROM dbo.s_DjTjPlan STP " & _
                " LEFT JOIN dbo.p_Project P ON STP.ProjGUID=P.ProjGUID" & _
                " LEFT JOIN dbo.myBusinessUnit BU ON STP.BUGUID=BU.BUGUID" & _
                " WHERE PlanGUID='" & Me._businessGUID & "'"

        dtTemp = MyDB.GetDataTable(strSQL)
        '获取当前计划中楼栋的GUID
        dtBldGUID = MyDB.GetDataTable("SELECT DISTINCT BldGUID FROM dbo.s_DjTjResult WHERE PlanGUID='" & Me._businessGUID & "' ")
        For i = 0 To dtBldGUID.Rows.Count - 1
            If i = 0 Then
                strBldGUID = dtBldGUID.Rows(i)("BldGUID").ToString
            Else
                strBldGUID &= "," + dtBldGUID.Rows(i)("BldGUID").ToString
            End If
        Next
        strBldGUID = "'" + strBldGUID.Replace(",", "','") + "'"

        If dtTemp.Rows.Count > 0 Then
            '根节点业务GUID属性
            _xml.SelectSingleNode("/BusinessType").Attributes.GetNamedItem("BusinessGUID").Value = Me._businessGUID
            '主记录
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='底价GUID']").InnerText = Me._businessGUID
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='调价方案']").InnerText = dtTemp.Rows(0)("PlanName").ToString
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='公司名称']").InnerText = dtTemp.Rows(0)("BUName").ToString
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='项目名称']").InnerText = dtTemp.Rows(0)("ProjName").ToString
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='申请人']").InnerText = HttpContext.Current.Session("UserName")
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='申请日期']").InnerText = DateTime.Now.ToString("yyyy-MM-dd")
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='调价说明']").InnerText = dtTemp.Rows(0)("Remark").ToString

        End If
        '获取列表信息
        strSQLDetail.Append("SELECT a.*,b.zts,b.zarea" & _
                            " FROM (SELECT pb.bldguid," & _
                            " pb.bldfullname,pb.parentcode + '.' + pb.bldcode AS x," & _
                            " COUNT(1) AS ts," & _
                            " Isnull(Sum(STR.TotalDj),0)/10000 AS total," & _
                            " Isnull(Sum(STR.TotalDj) / SUM(STR.bldarea),0) AS price," & _
                            " Isnull(Sum(STR.bldarea),0) AS area" & _
                            " FROM s_DjTjResult STR " & _
                            " LEFT JOIN p_room PR ON STR.RoomGUID=PR.RoomGUID" & _
                            " LEFT JOIN p_building PB ON STR.BldGUID=PB.BldGUID" & _
                            " WHERE STR.PlanGUID='" & Me._businessGUID & "'" & _
                            " GROUP BY PB.ParentCode+'.'+PB.BldCode,PB.BldFullName,PB.BldGUID) a" & _
                            " LEFT JOIN (SELECT bldguid,Count(1) AS zts,SUM(pr.bldarea) AS zarea" & _
                            " FROM p_room pr WHERE bldguid IN (" & strBldGUID & ")" & _
                            " GROUP BY bldguid) b ON a.bldguid = b.bldguid " & _
                            " ORDER BY a.x ASC")

        Dim group As XmlNode = _xml.SelectSingleNode("/BusinessType/Item/Group")
        Dim item As XmlNode = group.SelectSingleNode("Item")
        '删除所有Group中的Item子节点
        Dim items As XmlNodeList = group.SelectNodes("Item")
        For z = 0 To items.Count - 1
            group.RemoveChild(items(z))
        Next
        dtDetail = MyDB.GetDataTable(strSQLDetail.ToString)

        If dtDetail.Rows.Count > 0 Then
            '
            Dim strDiff As String = String.Empty
            For i = 0 To dtDetail.Rows.Count - 1
                strTotalChg = "-"
                strPriceChg = "-"

                '获取楼栋最近调价档案中的单价和总价
                strLastPlanGUID = MyDB.GetDataItemString("SELECT TOP 1 s_DjTjPlan.PlanGUID FROM s_DjTjPlan " & _
                                    "INNER JOIN dbo.s_DjTjResult ON dbo.s_DjTjPlan.PlanGUID = dbo.s_DjTjResult.PlanGUID " & _
                                    "WHERE Status='已执行' AND BldGUID='" & dtDetail.Rows(i)("BldGUID").ToString & "'" & _
                                    "ORDER BY ZdDate DESC")
                strSQLLastPlan = " SELECT " & _
                                " Isnull(SUM(STR.TotalDj),0)/10000 AS total," & _
                                " Isnull(SUM(STR.TotalDj) / SUM(STR.bldarea),0) AS price," & _
                                " Isnull(SUM(STR.bldarea),0) AS bldarea" & _
                                " FROM s_DjTjResult STR " & _
                                " LEFT JOIN p_room PR ON STR.RoomGUID=PR.RoomGUID" & _
                                " LEFT JOIN p_building PB ON STR.BldGUID=PB.BldGUID" & _
                                " WHERE STR.PlanGUID='" & strLastPlanGUID & "' AND STR.BldGUID='" & dtDetail.Rows(i)("BldGUID").ToString & "'"

                dtLastPlan = MyDB.GetDataTable(strSQLLastPlan)
                '单价与总价较上一版本比较
                If dtLastPlan.Rows.Count > 0 Then
                    strTotalChg = Format((Decimal.Parse(dtDetail.Rows(i)("total").ToString) - Decimal.Parse(dtLastPlan.Rows(0)("total").ToString)), "###,0.00")
                    'strPriceChg = Math.Round((Single.Parse(dtDetail.Rows(i)("price").ToString) - Single.Parse(dtLastPlan.Rows(0)("price").ToString)), 2)
                    strPriceChg = Format((Decimal.Parse(dtDetail.Rows(i)("price").ToString) - Decimal.Parse(dtLastPlan.Rows(0)("total").ToString) * 10000 / Decimal.Parse(dtLastPlan.Rows(0)("bldarea").ToString)), "###,0.00")
                    strHjTotalChg = (Decimal.Parse(strTotalChg) + Decimal.Parse(strHjTotalChg)).ToString
                    strHjLastArea = (Decimal.Parse(dtLastPlan.Rows(0)("bldarea").ToString) + Decimal.Parse(strHjLastArea)).ToString
                    strHjLastTotal = (Decimal.Parse(dtLastPlan.Rows(0)("total").ToString) + Decimal.Parse(strHjLastTotal)).ToString
                End If
                '循环插入列表数据
                Dim newXmlNode As XmlElement = item.Clone()
                newXmlNode.SelectSingleNode("Domain[@name='楼栋']").InnerText = dtDetail.Rows(i)("BldFullName").ToString
                newXmlNode.SelectSingleNode("Domain[@name='总套数']").InnerText = dtDetail.Rows(i)("zts").ToString
                newXmlNode.SelectSingleNode("Domain[@name='总建筑面积(平米)']").InnerText = dtDetail.Rows(i)("zarea").ToString
                newXmlNode.SelectSingleNode("Domain[@name='建筑面积(平米)']").InnerText = dtDetail.Rows(i)("area").ToString
                newXmlNode.SelectSingleNode("Domain[@name='套数']").InnerText = dtDetail.Rows(i)("ts").ToString
                newXmlNode.SelectSingleNode("Domain[@name='建筑单价(元/平米)']").InnerText = dtDetail.Rows(i)("price").ToString
                newXmlNode.SelectSingleNode("Domain[@name='单价较上版变化(元/平米)']").InnerText = strPriceChg
                newXmlNode.SelectSingleNode("Domain[@name='总价']").InnerText = dtDetail.Rows(i)("total").ToString
                newXmlNode.SelectSingleNode("Domain[@name='总价较上版本变化']").InnerText = strTotalChg
                group.AppendChild(newXmlNode)

                '存储重复列表域的数据
                strDiff &= dtDetail.Rows(i)("BldFullName").ToString
                strDiff &= "!" & dtDetail.Rows(i)("zts").ToString
                strDiff &= "!" & dtDetail.Rows(i)("zarea").ToString
                strDiff &= "!" & dtDetail.Rows(i)("area").ToString
                strDiff &= "!" & dtDetail.Rows(i)("ts").ToString
                strDiff &= "!" & dtDetail.Rows(i)("price").ToString
                strDiff &= "!" & strPriceChg
                strDiff &= "!" & dtDetail.Rows(i)("total").ToString
                strDiff &= "!" & strTotalChg
            Next
            '处理重复列表域的比较问题
            _xml.SelectSingleNode("/BusinessType/Item/Domain[@name='本次定价审批信息比较域']").InnerText = strDiff.GetHashCode().ToString()

            '统计合计信息，并插入合计行
            strSQL = "SELECT COUNT(1) AS zts,SUM(pr.bldarea) AS zarea FROM p_room pr where bldguid IN (" & strBldGUID & ")"
            dtTemp = MyDB.GetDataTable(strSQL)
            strHjZts = dtTemp.Rows(0)("zts").ToString
            strHjZBldArea = dtTemp.Rows(0)("zarea").ToString
            strSQL = "SELECT COUNT(1) AS ts,Isnull(SUM(str.bldarea),0) AS area,Isnull(SUM(str.totaldj),0) / 10000 AS total, " & _
                    " Isnull(SUM(str.totaldj) / SUM(str.bldarea),0) AS price FROM s_djtjresult str WHERE str.planguid = '" & Me._businessGUID & "'"
            dtTemp = MyDB.GetDataTable(strSQL)
            strHjTs = dtTemp.Rows(0)("ts").ToString
            strHjBldArea = dtTemp.Rows(0)("area").ToString
            strHjPrice = dtTemp.Rows(0)("price").ToString
            strHjTotal = dtTemp.Rows(0)("total").ToString
            '单价较上一版本变化(本次调价方案中(总价之和/建筑面积之和)-包含本次调价方案中的楼栋的上一版本中的(总价之和/建筑面积之和))
            If strHjLastArea = "0.00" Then
                strHjPriceChg = "0.00"
            Else

                'strHjPriceChg = Math.Round(((Single.Parse(strHjTotal) / Single.Parse(strHjBldArea) - Single.Parse(strHjLastTotal) / Single.Parse(strHjLastArea)) * 10000), 2)
                'strHjPriceChg = Math.Round(Single.Parse(strHjTotal) * 10000 / Single.Parse(strHjBldArea), 2) - Math.Round(Single.Parse(strHjLastTotal) * 10000 / Single.Parse(strHjLastArea), 2)
                strHjPriceChg = Math.Round((Decimal.Parse(strHjPrice) - Decimal.Parse(strHjLastTotal) * 10000 / Decimal.Parse(strHjLastArea)), 2)
            End If
            '插入合计行
            Dim newXmlNodeHj As XmlElement = item.Clone()
            newXmlNodeHj.SelectSingleNode("Domain[@name='楼栋']").InnerText = "合计"
            newXmlNodeHj.SelectSingleNode("Domain[@name='总套数']").InnerText = strHjZts
            newXmlNodeHj.SelectSingleNode("Domain[@name='总建筑面积(平米)']").InnerText = strHjZBldArea
            newXmlNodeHj.SelectSingleNode("Domain[@name='建筑面积(平米)']").InnerText = strHjBldArea
            newXmlNodeHj.SelectSingleNode("Domain[@name='套数']").InnerText = strHjTs
            newXmlNodeHj.SelectSingleNode("Domain[@name='建筑单价(元/平米)']").InnerText = strHjPrice
            newXmlNodeHj.SelectSingleNode("Domain[@name='单价较上版变化(元/平米)']").InnerText = Format(Decimal.Parse(strHjPriceChg), "###,0.00")
            newXmlNodeHj.SelectSingleNode("Domain[@name='总价']").InnerText = strHjTotal
            newXmlNodeHj.SelectSingleNode("Domain[@name='总价较上版本变化']").InnerText = Format(Decimal.Parse(strHjTotalChg), "###,0.00")
            group.AppendChild(newXmlNodeHj)

        End If
            Return 0

    End Function

    '功能：发起“底价调价计划”的审批流程
    Public Overrides Function Initiate() As String
        Dim strSQL, strRoomGUID As New StringBuilder
        Dim strResult, strRoomSQL As String
        Dim dt As DataTable
        Dim i As Integer
        '获取调价方案中的房间GUID
        strRoomSQL = "SELECT RoomGUID FROM s_DjTjResult WHERE PlanGUID='" & Me._businessGUID & "'"
        dt = MyDB.GetDataTable(strRoomSQL)
        If dt.Rows.Count > 0 Then
            For i = 0 To dt.Rows.Count - 1
                If i = 0 Then
                    strRoomGUID.Append(dt.Rows(i).Item("RoomGUID").ToString)
                Else
                    strRoomGUID.Append("," + dt.Rows(i).Item("RoomGUID").ToString)
                End If
            Next
        End If
        strRoomGUID = strRoomGUID.Replace(",", "','")
        '更新调价方案状态
        strSQL.Append("UPDATE s_DjTjPlan SET Status = '审批中',ApplyState=NULL WHERE PlanGUID = '" & Me._businessGUID & "';")
        '更新房间IsDj2AreaLock
        strSQL.Append(" UPDATE p_Room SET IsDj2AreaLock=1 WHERE RoomGUID IN ('" & strRoomGUID.ToString & "') ")

        '提交更新语句
        If MyDB.ExecSQL(strSQL.ToString) > 0 Then
            strResult = "<xml result=""true"" errormessage=""""></xml>"
        Else
            strResult = "<xml result=""false"" errormessage=""审批失败，请稍后再试！""></xml>"
        End If

        Return strResult

    End Function

    '功能：根据XML进行数据对“底价调价计划”进行归档
    Public Overrides Function SaveData() As String
        Dim strSQL As String
        Dim strResult As String
        Dim strApproveBy As String = HttpContext.Current.Session("UserName")

        strSQL = "UPDATE s_DjTjPlan SET" & _
                 " Status='已审批'," & _
                 " Shr='" & strApproveBy & "'," & _
                 " ShDate=CONVERT(varchar(10),getdate(),121)" & _
                 " WHERE PlanGUID = '" & Me._businessGUID & "'"

        '提交更新语句
        If MyDB.ExecSQL(strSQL) > 0 Then
            strResult = "<xml result=""true"" errormessage=""""></xml>"
        Else
            strResult = "<xml result=""false"" errormessage=""审批失败，请稍后再试！""></xml>"
        End If

        Return strResult
    End Function

    '功能：不通过处理
    Public Overrides Function SaveData_NoPass() As String
        Dim strSQL, strRoomGUID As New StringBuilder
        Dim strResult, strRoomSQL As String
        Dim dt As DataTable
        Dim i As Integer
        '获取调价方案中的房间GUID
        strRoomSQL = "SELECT RoomGUID FROM s_DjTjResult WHERE PlanGUID='" & Me._businessGUID & "'"
        dt = MyDB.GetDataTable(strRoomSQL)
        If dt.Rows.Count > 0 Then
            For i = 0 To dt.Rows.Count - 1
                If i = 0 Then
                    strRoomGUID.Append(dt.Rows(i).Item("RoomGUID").ToString)
                Else
                    strRoomGUID.Append("," + dt.Rows(i).Item("RoomGUID").ToString)
                End If
            Next
        End If
        strRoomGUID = strRoomGUID.Replace(",", "','")
        strSQL.Append("UPDATE s_DjTjPlan SET Status = '待审批',ApplyState='不通过' WHERE PlanGUID = '" & Me._businessGUID & "';")
        '更新房间IsDj2AreaLock
        strSQL.Append(" UPDATE p_Room SET IsDj2AreaLock=0 WHERE RoomGUID IN ('" & strRoomGUID.ToString & "') ")

        '提交更新语句
        If MyDB.ExecSQL(strSQL.ToString) Then
            strResult = "<xml result=""true"" errormessage=""""></xml>"
        Else
            strResult = "<xml result=""false"" errormessage=""审批失败，请稍后再试！""></xml>"
        End If

        '返回执行是否成功的信息
        Return strResult
    End Function

    '功能：审批作废处理
    Public Overrides Function SaveData_Cancel() As String
        Dim strSQL, strRoomGUID As New StringBuilder
        Dim strResult, strRoomSQL As String
        Dim dt As DataTable
        Dim i As Integer
        '获取调价方案中的房间GUID
        strRoomSQL = "SELECT RoomGUID FROM s_DjTjResult WHERE PlanGUID='" & Me._businessGUID & "'"
        dt = MyDB.GetDataTable(strRoomSQL)
        If dt.Rows.Count > 0 Then
            For i = 0 To dt.Rows.Count - 1
                If i = 0 Then
                    strRoomGUID.Append(dt.Rows(i).Item("RoomGUID").ToString)
                Else
                    strRoomGUID.Append("," + dt.Rows(i).Item("RoomGUID").ToString)
                End If
            Next
        End If
        strRoomGUID = strRoomGUID.Replace(",", "','")
        strSQL.Append("UPDATE s_DjTjPlan SET Status = '待审批',ApplyState='作废' WHERE PlanGUID = '" & Me._businessGUID & "';")
        '更新房间IsDj2AreaLock
        strSQL.Append(" UPDATE p_Room SET IsDj2AreaLock=0 WHERE RoomGUID IN ('" & strRoomGUID.ToString & "') ")

        '提交更新语句
        If MyDB.ExecSQL(strSQL.ToString) Then
            strResult = "<xml result=""true"" errormessage=""""></xml>"
        Else
            strResult = "<xml result=""false"" errormessage=""审批失败，请稍后再试！""></xml>"
        End If

        '返回执行是否成功的信息
        Return strResult
    End Function

    '功能：发起撤回审批流程
    Public Overrides Function InitiateWithDrawn() As String
        Dim strSQL, strRoomGUID As New StringBuilder
        Dim strResult, strRoomSQL As String
        Dim dt As DataTable
        Dim i As Integer
        '获取调价方案中的房间GUID
        strRoomSQL = "SELECT RoomGUID FROM s_DjTjResult WHERE PlanGUID='" & Me._businessGUID & "'"
        dt = MyDB.GetDataTable(strRoomSQL)
        If dt.Rows.Count > 0 Then
            For i = 0 To dt.Rows.Count - 1
                If i = 0 Then
                    strRoomGUID.Append(dt.Rows(i).Item("RoomGUID").ToString)
                Else
                    strRoomGUID.Append("," + dt.Rows(i).Item("RoomGUID").ToString)
                End If
            Next
        End If
        strRoomGUID = strRoomGUID.Replace(",", "','")
        strSQL.Append("UPDATE s_DjTjPlan SET Status = '待审批' WHERE PlanGUID = '" & Me._businessGUID & "';")
        '更新房间IsDj2AreaLock
        strSQL.Append(" UPDATE p_Room SET IsDj2AreaLock=0 WHERE RoomGUID IN ('" & strRoomGUID.ToString & "') ")

        '提交更新语句
        If MyDB.ExecSQL(strSQL.ToString) Then
            strResult = "<xml result=""true"" errormessage=""""></xml>"
        Else
            strResult = "<xml result=""false"" errormessage=""审批失败，请稍后再试！""></xml>"
        End If

        '返回执行是否成功的信息
        Return strResult

    End Function


End Class
